package gew.services;

import gew.beans.DataFacade2;
import gew.data.DataForLoad;
import gew.data.DimensionValue;
import gew.data.LoadRequest;
import gew.metadata.CustomSql;
import gew.metadata.Queries;
import gew.sql.SqlTemplates;
import gew.utils.SqlUtils;
import java.io.Serializable;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import javax.ejb.EJB;
import javax.ejb.Stateless;

@Stateless
public class QueryService implements Serializable {

    @EJB
    private DataFacade2 df;

    public List<DimensionValue> getDimensionValueItems(Queries queryType, String locale) {
        String q1 = SqlUtils.EMPTY;
        switch (queryType){
            case CommodityDimensionValue:
                q1 = SqlTemplates.getSql(SqlTemplates.DIMENSION_COMMODITY);break;
            case CommodityGroupTypeDimensionValue:
                q1 = SqlTemplates.getSql(SqlTemplates.DIMENSION_COMMODITY_GROUP_TYPE);break;
            case CountryDimensionValue:
                q1 = SqlTemplates.getSql(SqlTemplates.DIMENSION_COUNTRY);break;
            case IndicatorDimensionValue:
                q1 = SqlTemplates.getSql(SqlTemplates.DIMENSION_INDICATOR);break;
            case PeriodDimensionValue:
                q1 = SqlTemplates.getSql(SqlTemplates.DIMENSION_PERIOD);break;                
            case TradeFlowDimensionValue:
                q1 = SqlTemplates.getSql(SqlTemplates.DIMENSION_TRADE_FLOW);break;          
            case SectionDimensionValue:
                q1 = SqlTemplates.getSql(SqlTemplates.DIMENSION_SECTION);break;
        }
        q1 = q1.replace("@locale@", locale);        
        return df.getDimensionValues(q1);
    }

    public static String getCellValue(Object[] row, int colIndex, String ifNullValue) {
        if (row.length > colIndex) {
            return row[colIndex] == null ? ifNullValue : row[colIndex].toString();
        } else {
            return ifNullValue;
        }
    }

    public static Integer getCellValue(Object[] row, int colIndex, Integer ifNullValue) {
        if (row.length > colIndex && row[colIndex] != null) {
            if (row[colIndex].getClass().equals(Boolean.class)) {
                return Boolean.parseBoolean(row[colIndex].toString()) ? 1 : 0;
            } else {
                return Integer.parseInt(row[colIndex].toString());
            }
        } else {
            return ifNullValue;
        }
    }

    public static Integer getCellValueFromBoolean(Object[] row, int colIndex, Integer ifNullValue) {
        if (row.length > colIndex) {
            return row[colIndex] == null ? ifNullValue : (Boolean.parseBoolean(row[colIndex].toString()) ? 1 : 0);
        } else {
            return ifNullValue;
        }
    }

    public static Long getCellValue(Object[] row, int colIndex, Long ifNullValue) {
        if (row.length > colIndex) {
            return row[colIndex] == null ? ifNullValue : Long.parseLong(row[colIndex].toString());
        } else {
            return ifNullValue;
        }
    }

    public static Float getCellValue(Object[] row, int colIndex, Float ifNullValue) {
        if (row.length > colIndex) {
            return row[colIndex] == null ? ifNullValue : Float.parseFloat(row[colIndex].toString());
        } else {
            return ifNullValue;
        }
    }

    public static Date getCellValue(Object[] row, int colIndex, Date ifNullValue, DateFormat df) {
        if (row.length > colIndex) {
            try {
                return row[colIndex] == null ? ifNullValue : df.parse(row[colIndex].toString());
            } catch (Exception e) {
                System.out.printf(e.getMessage());
                return ifNullValue;
            }
        } else {
            return ifNullValue;
        }
    }

    public DataForLoad getNextDataForLoad(Integer status) {
        String q1 = CustomSql.getDataForLoadNext(status);
        Object[] result = df.getSqlQueryFirstResult(q1);
        if (result == null) {
            return null;
        } else {
            return new DataForLoad(getCellValue(result, DataForLoad.SQL_ID, -1), getCellValue(result, DataForLoad.SQL_REPORTER_ID, -1), getCellValue(result, DataForLoad.SQL_PERIOD_ID, -1), getCellValue(result, DataForLoad.SQL_COMMODITY_GROUP_TYPE_ID, -1), getCellValue(result, DataForLoad.SQL_COUNT, -1), getCellValue(result, DataForLoad.SQL_STATUS, -1));
        }
    }

    public DataForLoad getNextDataForLoad(Integer status, Integer reporterId, Integer commodityGroupTypeId, Integer maxCount, String notInIds) {
        String q1 = CustomSql.getDataForLoadNext(status, reporterId, commodityGroupTypeId, maxCount, notInIds);
        Object[] result = df.getSqlQueryFirstResult(q1);
        if (result == null) {
            return null;
        } else {
            return new DataForLoad(getCellValue(result, DataForLoad.SQL_ID, -1), getCellValue(result, DataForLoad.SQL_REPORTER_ID, -1), getCellValue(result, DataForLoad.SQL_PERIOD_ID, -1), getCellValue(result, DataForLoad.SQL_COMMODITY_GROUP_TYPE_ID, -1), getCellValue(result, DataForLoad.SQL_COUNT, -1), getCellValue(result, DataForLoad.SQL_STATUS, -1));
        }
    }

    public List<DataForLoad> getDataForLoadGroup(Integer reporterId, Integer periodId, Integer commodityGroupTypeId, Integer status) {
        String q1 = CustomSql.getDataForLoadGroup(reporterId, periodId, commodityGroupTypeId, status);
        List<Object[]> results = df.getSqlQueryResults(q1);

        ArrayList<DataForLoad> result = new ArrayList<>();
        for (Integer i = 0; i < results.size(); i++) {
            result.add(
                    new DataForLoad(getCellValue(results.get(i), DataForLoad.SQL_ID, -1), getCellValue(results.get(i), DataForLoad.SQL_REPORTER_ID, -1), getCellValue(results.get(i), DataForLoad.SQL_PERIOD_ID, -1), getCellValue(results.get(i), DataForLoad.SQL_COMMODITY_GROUP_TYPE_ID, -1), getCellValue(results.get(i), DataForLoad.SQL_COUNT, -1), getCellValue(results.get(i), DataForLoad.SQL_STATUS, -1))
            );
        }
        return result;
    }

    public String getDataForLoadGroupIds(Integer reporterId, Integer periodId, Integer commodityGroupTypeId, Integer status) {
        String q1 = CustomSql.getDataForLoadGroupIds(reporterId, periodId, commodityGroupTypeId, status);
        Object result = df.getSqlQuerySingleResult(q1);
        if (result == null) {
            return "-1";
        } else {
            return result.toString();
        }
    }

    public Integer updateDataForLoadGroupStatus(String ids, Integer status) {
        String q1 = CustomSql.getDataForLoadGroupIdsUpdateStatus(ids, status);
        Integer result = df.executeUpdate(q1);
        return result;
    }

    public Integer insertDataForLoadGroupToLoadRequests(String ids, Boolean groupByPartners) {
        String q1 = CustomSql.getDataForLoadGroupsToLoadRequest(ids, groupByPartners);
        Integer result = df.executeUpdate(q1);
        return result;
    }

    public int insertLocalLoadRequest(String fileName, Integer status) {
        String q1 = CustomSql.getInsertLocalLoadRequest(fileName, status);
        return df.executeUpdate(q1);
    }

    public LoadRequest getNextLoadRequest(String fileName, Integer status) {
        String q1 = CustomSql.getLoadRequestNext(fileName, status);
        return df.getLoadRequest(q1);
    }

    public LoadRequest getNextLoadRequest(Integer status) {
        String q1 = CustomSql.getLoadRequestNext(status);
        Object[] result = df.getSqlQueryFirstResult(q1);
        if (result == null) {
            return null;
        } else {
            return new LoadRequest(getCellValue(result, LoadRequest.SQL_ID, -1), getCellValue(result, LoadRequest.SQL_URL, ""), getCellValue(result, LoadRequest.SQL_FILENAME, ""), getCellValue(result, LoadRequest.SQL_START, null, DateFormat.getInstance()), getCellValue(result, LoadRequest.SQL_END, null, DateFormat.getInstance()), getCellValue(result, LoadRequest.SQL_STATUS, -1), getCellValue(result, LoadRequest.SQL_COUNT, -1));
        }
    }

    public int updateLoadRequestStatus(Integer id, Integer status) {
        String q1 = SqlUtils.SEMICOLON;
        if (Objects.equals(status, LoadRequest.STATUS_EXECUTION)
                || Objects.equals(status, LoadRequest.STATUS_LOCAL_EXECUTION)) {
            q1 = CustomSql.getLoadRequestUpdateStatusToExecution(id, status);
        } else if (Objects.equals(status, LoadRequest.STATUS_COMPLETED)
                || Objects.equals(status, LoadRequest.STATUS_LOCAL_COMPLETED)) {
            q1 = CustomSql.getLoadRequestUpdateStatusToExecuted(id, status);
        } else if (Objects.equals(status, LoadRequest.STATUS_LOAD_ERROR)
                || Objects.equals(status, LoadRequest.STATUS_LOCAL_LOAD_ERROR)) {
            q1 = CustomSql.getLoadRequestUpdateStatusToFailed(id, status);
        } else if (Objects.equals(status, LoadRequest.STATUS_READY)
                || Objects.equals(status, LoadRequest.STATUS_LOCAL_READY)) {
            q1 = CustomSql.getLoadRequestUpdateStatusToReady(id, status);
        }
        return df.executeUpdate(q1);
    }

    public int updateLoadRequestCountAsIs(Integer loadRequestId) {
        String q1 = CustomSql.getLoadRequestUpdateCountAsIs(loadRequestId);
        return df.executeUpdate(q1);
    }

    public int alterPartitionsFctTrade(Integer loadRequestId) {
        String q1 = CustomSql.getAlterPartitionsFctTrade(loadRequestId);
        List<String> result = df.executeQueryToListOfString(q1);
        for (Integer i = 0; i < result.size(); i++) {
            df.executeStatements(result.get(i));
        }
        return result.size();
    }

    public int deleteFromFctTrade(Integer loadRequestId) {
        String q1 = CustomSql.getDistinctInpTradeDataTargetTable(loadRequestId);
        List<String> result = df.executeQueryToListOfString(q1);
        for (Integer i = 0; i < result.size(); i++) {
            df.executeStatements(result.get(i));
        }
        return result.size();
    }

    public String getChangeGroupCommodityUrlCodes(String codes, String group) {
        String q1 = CustomSql.getChangeGroupCommodityUrlCodes(codes, group);
        String result = df.getSqlQuerySingleResult(q1).toString();
        return result;
    }

    public boolean tradeLoadPreparation(Integer loadRequestId, String fileName) {
        String q1 = CustomSql.getTradeLoadPreparation(loadRequestId, fileName);
        return df.executeStatements(q1);
    }

    public boolean tradeLoadExecution(Integer loadRequestId) {
        String q1 = CustomSql.getAlterPartitionsFctTrade(loadRequestId);
        List<String> partitions = df.executeQueryToListOfString(q1);
        String q2 = CustomSql.getDistinctInpTradeDataTargetTable(loadRequestId);
        List<String> deletions = df.executeQueryToListOfString(q2);
        String q3 = CustomSql.getTradeLoadExecution(loadRequestId,
                String.join(SqlUtils.SEMICOLON, partitions),
                String.join(SqlUtils.SEMICOLON, deletions));
        return df.executeStatements(q3);
    }

}
